Mikmak MySQL DML Supplier

CRUD stored procedures voor de Supplier tabel. De afspraak is dat de namen voor de stored procedurs beginnen met de naam van de tabel gevolgd door de naam van de CRUD handeling. De naam wordt in pascalnotatie geschreven.

Probleem

We moeten de gegevens van een basiseenheid kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam, code en contactpersoon. Voor het maken van de stored procedures baseren we ons op het Mikmak logisch model.

Design

Naam	Beschrijving
SupplierInsert	bevat 1 OUTPUT parameter om de nieuw Id te retourneren naar het calling programma
SupplierUpdate	deze stored procedure updatet alle kolommen van de rij met de opgegeven Id
SupplierSelectOne	lees 1 rij in uit de tabel op basis van de Id, neem alle kolommen mee voor het detail venster
SupplierSelectAll	lees alle rijen in uit de tabel maar alleen de kolommen die we in de lijst willen laten zien
SupplierSelectByCode	ees 1 rij in uit de tabel op basis van de Name maar alleen de kolommen die we in de lijst willen laten zien
SupplierSelectByName	ees 1 rij in uit de tabel op basis van de Name maar alleen de kolommen die we in de lijst willen laten zien
SupplierSelectByContact	ees 1 rij in uit de tabel op basis van de Name maar alleen de kolommen die we in de lijst willen laten zien

Oplossing

Insert

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 07:05:33 PM
-- DML Insert Stored Procedure for Supplier 
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS SupplierInsert;
DELIMITER //
CREATE PROCEDURE `SupplierInsert`
(
	IN pCode NVARCHAR (10) ,
	IN pName NVARCHAR (255) ,
	IN pContact NVARCHAR (255) ,
	IN pAddress NVARCHAR (255) ,
	IN pCity NVARCHAR (255) ,
	IN pRegion NVARCHAR (80) ,
	IN pPostalCode VARCHAR (20) ,
	IN pIdCountry INT ,
	IN pPhone VARCHAR (40) ,
	IN pMobile VARCHAR (40) ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Supplier`
	(
		`Supplier`.`Code`,
		`Supplier`.`Name`,
		`Supplier`.`Contact`,
		`Supplier`.`Address`,
		`Supplier`.`City`,
		`Supplier`.`Region`,
		`Supplier`.`PostalCode`,
		`Supplier`.`IdCountry`,
		`Supplier`.`Phone`,
		`Supplier`.`Mobile`
	)
	VALUES
	(
		pCode,
		pName,
		pContact,
		pAddress,
		pCity,
		pRegion,
		pPostalCode,
		pIdCountry,
		pPhone,
		pMobile
	);
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

De procedure testen

call SupplierInsert('BE00001ICT', 'ModernWays',
    'Jef Inghelbrecht', 'Rue du Petit Bois 38', 'Rièzes',
    'Henegouwen', '6400', 
    (select Id from Country where Code='XX'),
    '064546765', '0467657898', @NewId);
select @NewId;

Als ik een leveverancier met een niet bestaande IdCountry wil toevoegen zal ik een foutmelding krijgen omwille van de not null constraint op die kolom:

MySQL SupplierInsert NOT NULL IdCountry constraint

Met een bestaande IdCountry lukt het wel.

call SupplierInsert('BE00001ICT', 'ModernWays',
    'Jef Inghelbrecht', 'Rue du Petit Bois 38', 'Rièzes',
    'Henegouwen', '6400', 
    (select Id from Country where Code='BE'),
    '064546765', '0467657898', @NewId);
select @NewId;

MyQL SupplierInsert Stored Procedure Test

Update

De procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 07:05:33 PM
-- DML Update Stored Procedure for Supplier
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS SupplierUpdate;
DELIMITER //
CREATE PROCEDURE `SupplierUpdate`
(
	pCode NVARCHAR (10) ,
	pName NVARCHAR (255) ,
	pContact NVARCHAR (255) ,
	pAddress NVARCHAR (255) ,
	pCity NVARCHAR (255) ,
	pRegion NVARCHAR (80) ,
	pPostalCode VARCHAR (20) ,
	pIdCountry INT ,
	pPhone VARCHAR (40) ,
	pMobile VARCHAR (40) ,
	pId INT 
)
BEGIN
UPDATE `Supplier`
	SET
		`Code` = pCode,
		`Name` = pName,
		`Contact` = pContact,
		`Address` = pAddress,
		`City` = pCity,
		`Region` = pRegion,
		`PostalCode` = pPostalCode,
		`IdCountry` = pIdCountry,
		`Phone` = pPhone,
		`Mobile` = pMobile
	WHERE `Supplier`.`Id` = pId;
END //
DELIMITER ;

De procedure testen

Voeg eerst Nederland toe als het land nog niet in de Country tabel zit:

call CountryInsert ('NL', 52.5, 5.75, 'Netherlands', 1, @NewId);
select @NewId;

Wijzig daarna de IdCountry van de net toegevoegde leverancier:

use Webshop;
call SupplierUpdate('BE00001ICT', 'ModernWays',
    'Jef Inghelbrecht', 'Rue du Petit Bois 38', 'Rièzes',
    'Henegouwen', '6400', 
    (select Id from Country where Code='NL'),
    '064546765', '0467657898', 
    (select Id from Supplier where Name='ModernWays'));
    
select * from supplier

Delete

De procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 07:05:33 PM
-- DML Delete Stored Procedure for Supplier 
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS SupplierDelete;
DELIMITER //
CREATE PROCEDURE `SupplierDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Supplier`
	WHERE `Supplier`.`Id` = pId;
END //
DELIMITER ;

De procedure testen

use Webshop;
call SupplierDelete (
    (select Id from Supplier 
    where Name='ModernWays'));
    
select * from supplier

We voegen de leverancier opnieuw toe.

SelectOne

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 07:05:33 PM
-- DML SelectOne Stored Procedure for Supplier 
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS SupplierSelectOne;
DELIMITER //
CREATE PROCEDURE `SupplierSelectOne`
(
     pId INT 
)
BEGIN
SELECT `Supplier`.`Code`,
        `Supplier`.`Name`,
        `Supplier`.`Contact`,
        `Supplier`.`Address`,
        `Supplier`.`City`,
        `Supplier`.`Region`,
        `Supplier`.`PostalCode`,
        `Supplier`.`IdCountry`,
        `Supplier`.`Phone`,
        `Supplier`.`Mobile`,
        `Country`.`Name` as `CountryName`
    FROM `Supplier`
    INNER JOIN `Country`
        ON `Supplier`.`IdCountry` = `Country`.`Id`
    WHERE `Supplier`.`Id` = pId;
END //
DELIMITER ;

De procedure testen

use Webshop;
call SupplierSelectOne (
    (select Id from Supplier 
    where Name='ModernWays'));

SelectAll

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 07:05:33 PM
-- DML SelectAll Stored Procedure for table Supplier 
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS SupplierSelectAll;
DELIMITER //
CREATE PROCEDURE `SupplierSelectAll`
(
)
BEGIN
SELECT `Supplier`.`Code`,
	`Supplier`.`Name`,
	`Country`.`Name` as `CountryName`,
	`Supplier`.`IdCountry`,
	`Supplier`.`Id`
	FROM `Supplier`
	INNER JOIN `Country`
		ON `Supplier`.`IdCountry` = `Country`.`Id`
	ORDER BY `Name`;
END //
DELIMITER ;

De stored procedure testen

SelectByCode

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 07:05:33 PM
-- DML SelectByCode Stored Procedure for table Supplier
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS SupplierSelectByCode;
DELIMITER //
CREATE PROCEDURE `SupplierSelectByCode`
(
	 pCode NVARCHAR (10) 
)
BEGIN
SELECT `Supplier`.`Code`,
	`Supplier`.`Name`,
	`Country`.`Name` as `CountryName`,
	`Supplier`.`IdCountry`,
	`Supplier`.`Id`

	FROM `Supplier`
	INNER JOIN `Country`
		ON `Supplier`.`IdCountry` = `Country`.`Id`
	WHERE `Supplier`.`Code` = pCode
	ORDER BY `Supplier`.`Code`;
END //
DELIMITER ;

De stored procedure testen

SelectByName

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 07:05:33 PM
-- DML SelectByName Stored Procedure for table Supplier
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS SupplierSelectByName;
DELIMITER //
CREATE PROCEDURE `SupplierSelectByName`
(
	 pName NVARCHAR (255) 
)
BEGIN
SELECT `Supplier`.`Code`,
	`Supplier`.`Name`,
	`Country`.`Name` as `CountryName`,
	`Supplier`.`IdCountry`,
	`Supplier`.`Id`

	FROM `Supplier`
	INNER JOIN `Country`
		ON `Supplier`.`IdCountry` = `Country`.`Id`
	WHERE `Supplier`.`Name` = pName
	ORDER BY `Supplier`.`Name`;
END //
DELIMITER ;

De stored procedure testen

SelectByContact

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 11th of January 2016 07:05:33 PM
-- DML SelectByContact Stored Procedure for table Supplier
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS SupplierSelectByContact;
DELIMITER //
CREATE PROCEDURE `SupplierSelectByContact`
(
	 pContact NVARCHAR (255) 
)
BEGIN
SELECT `Supplier`.`Code`,
	`Supplier`.`Name`,
	`Country`.`Name` as `CountryName`,
	`Supplier`.`IdCountry`,
	`Supplier`.`Id`

	FROM `Supplier`
	INNER JOIN `Country`
		ON `Supplier`.`IdCountry` = `Country`.`Id`
	WHERE `Supplier`.`Contact` = pContact
	ORDER BY `Supplier`.`Contact`;
END //
DELIMITER ;

De stored procedure testen

2016-01-11 21:43:24